﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
using VH6I_B4.ProductParsing;
using VH6I_B4.Datawarehouse;

namespace VH6I_B4.BronBestandReaders
{
    class ExcelReader : ABronBestandReader
    {
        public ExcelReader(DatawarehouseApi dwh)
            : base(dwh)
        {
        }

        //this method is an implementation of the read method in ABronBestandReader
        public override void read()
        {
            //declaration of method specific attributes
            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            object misValue;

            //initialization of method specific attributes
            xlApp = new Excel.Application();
            xlWorkBook = xlApp.Workbooks.Open(@"E:\Dropbox\HBO Informatica\Jaar 2\Periode 2\B4\Proftaak\Bronbestanden\KoffieBestandEuro20082009.xlsx", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item("Sheet1");
            misValue = System.Reflection.Missing.Value;

            //order to the readSheat method with appropriate sheet
            this.readWorksheet(xlWorkSheet);

            //cleanup of sheetsvalues and objects
            xlWorkBook.Close(true, misValue, misValue);
            xlApp.Quit();

            releaseObject(xlWorkSheet);
            releaseObject(xlWorkBook);
            releaseObject(xlApp);
        }

        //this method imports the data from the worksheat
        private void readWorksheet(Excel.Worksheet _xlWorkSheet)
        {
            //declaration of method specific attributes
            string rawProduct;
            string supermarket;
            int valueArrayLength;
            ProductParser productParser;
            Excel.Range excelRange;
            object[,] valueArray;

            //initialization of method specific attributes
            rawProduct = "";
            supermarket = "";
            productParser = new ProductParser();
            excelRange = _xlWorkSheet.UsedRange;
            valueArray = (object[,])excelRange.get_Value(Excel.XlRangeValueDataType.xlRangeValueDefault);
            valueArrayLength = valueArray.GetLength(0);

            //for-statement that runs through each row in the array with collected data
            for (int i = 1; i <= valueArrayLength; i++)
            {
                //if-statement that filters out any values that are empty
                if (!string.IsNullOrEmpty((string)valueArray[i, 2]))
                {
                    //declaration of loop specific attributess
                    string rawLine;

                    //initialization of loop specific attributes
                    rawLine = (string)valueArray[i, 2];

                    //[DEBUG] System.Diagnostics.Debug.WriteLine("De orignele waarde is: " + rawLine);

                    //TrimEnd is used because the spaces at the beginning of each value are used in the next if-statement
                    rawLine = rawLine.TrimEnd();

                    /*If-statement that searches for lines that start with three spaces. If they do then they are part of
                     * the data tree I am interested in. Another if-statement will then take out the category lines because
                     * they do not contain any valuable data for us. The rest of the data is send of to the parser, situated
                     * in the productparser package.
                     * 
                     * Lines that don't start with three spaces are names of supermarkets. They will be saved as such.*/
                    if (rawLine.Contains("   ") && !rawLine.Contains("CATEGORY"))
                    {
                        //tree data I am interested in will be trimmed before it is send to the parser
                        rawProduct = rawLine.Trim();

                        //[DEBUG] System.Diagnostics.Debug.WriteLine("De productregel is: " + rawLine);

                        //declaration of method specific attributes
                        double[] rowData;

                        //initialisation of method specific attributes
                        rowData = new double[6];

                        //send the rawproduct lines to the parser and save the result
                        Dictionary<ProductLevel, string> parserResult = productParser.Parse(rawProduct);

                        if (parserResult.ContainsKey(ProductLevel.BARCODE))
                        {
                            //check for values in the array
                            for (int j = 3; j <= 8; j++)
                            {
                                if (valueArray[i, j] != null)
                                {
                                    //there are values in the array so convert these to doubles
                                    rowData[j - 3] = (double)valueArray[i, j];
                                }
                                else
                                {
                                    //there are no values in the array so fill in the blanks with zero's
                                    rowData[j - 3] = 0;
                                }
                            }

                            //send the data to the database
                            System.Diagnostics.Debug.WriteLine("values = |" + rowData[0] + "|" + rowData[1] + "|" + rowData[2] + "|" + rowData[3] + "|" + rowData[4] + "|" + rowData[5]);

                            //2008
                            _dwh.SaveFact(FactType.EURO_SALES, parserResult, supermarket, 2008, 1, rowData[0]);
                            _dwh.SaveFact(FactType.EURO_SALES, parserResult, supermarket, 2008, 2, rowData[1]);
                            _dwh.SaveFact(FactType.EURO_SALES, parserResult, supermarket, 2008, 3, rowData[2]);
                            _dwh.SaveFact(FactType.EURO_SALES, parserResult, supermarket, 2008, 4, rowData[3]);
                            //2009
                            _dwh.SaveFact(FactType.EURO_SALES, parserResult, supermarket, 2009, 1, rowData[4]);
                            _dwh.SaveFact(FactType.EURO_SALES, parserResult, supermarket, 2009, 2, rowData[5]);
                        }

                        /*print the results of the parser
                        if (parserResults.ContainsKey(ProductLevel.BARCODE))
                        {
                            PrintLabels(parserResults, supermarket);
                        }*/
                    }
                    else if (!rawLine.Contains("   ") && !rawLine.Contains("CATEGORY"))
                    {
                        //supermarket names will be trimmed before they are used, just to be sure
                        supermarket = rawLine.Trim();
                        //[DEBUG] System.Diagnostics.Debug.WriteLine("De supermarkt is: " + rawLine);
                    }
                }
            }
        }

        //this method releases an object
        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                MessageBox.Show("Unable to release the Object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }
        }

        //this is a copy of Jeffrey's print debug method
        private void PrintLabels(Dictionary<ProductLevel, string> parserResults, string supermarket)
        {
            if (!string.IsNullOrEmpty(supermarket))
                // _txtBoxLog.AppendText("[CATEGORY]" + "\t" + _category + Environment.NewLine);
                System.Diagnostics.Debug.WriteLine("[SUPERMARKET]" + "\t" + supermarket);

            if (!string.IsNullOrEmpty(parserResults[ProductLevel.MAJOR_BRAND]))
                //_txtBoxLog.AppendText("[MAJOR.BRAND]" + "\t" + _majorBrand + Environment.NewLine);
                System.Diagnostics.Debug.WriteLine("[MAJOR.BRAND]" + "\t" + parserResults[ProductLevel.MAJOR_BRAND]);

            if (!string.IsNullOrEmpty(parserResults[ProductLevel.APPARAAT]))
                // _txtBoxLog.AppendText("[APPARAAT]" + "\t" + _apparaat + Environment.NewLine);
                System.Diagnostics.Debug.WriteLine("[APPARAAT]" + "\t\t" + parserResults[ProductLevel.APPARAAT]);

            if (!string.IsNullOrEmpty(parserResults[ProductLevel.TYPE]))
                //_txtBoxLog.AppendText("[TYPE]" + "\t\t" + _type + Environment.NewLine);
                System.Diagnostics.Debug.WriteLine("[TYPE]" + "\t\t\t" + parserResults[ProductLevel.TYPE]);

            if (!string.IsNullOrEmpty(parserResults[ProductLevel.BELEVING]))
                //_txtBoxLog.AppendText("[BELEVING]" + "\t" + _beleving + Environment.NewLine);
                System.Diagnostics.Debug.WriteLine("[BELEVING]" + "\t\t" + parserResults[ProductLevel.BELEVING]);

            if (!string.IsNullOrEmpty(parserResults[ProductLevel.SOORT]))
                //_txtBoxLog.AppendText("[SOORT]" + "\t\t" + _soort + Environment.NewLine);
                System.Diagnostics.Debug.WriteLine("[SOORT]" + "\t\t\t" + parserResults[ProductLevel.SOORT]);

            if (!string.IsNullOrEmpty(parserResults[ProductLevel.FORMAAT]))
                // _txtBoxLog.AppendText("[FORMAAT]" + "\t" + _formaat + Environment.NewLine);
                System.Diagnostics.Debug.WriteLine("[FORMAAT]" + "\t\t" + parserResults[ProductLevel.FORMAAT]);

            if (!string.IsNullOrEmpty(parserResults[ProductLevel.SMAAK]))
                //_txtBoxLog.AppendText("[SMAAK]" + "\t\t" + _smaak + Environment.NewLine);
                System.Diagnostics.Debug.WriteLine("[SMAAK]" + "\t\t\t" + parserResults[ProductLevel.SMAAK]);

            if (!string.IsNullOrEmpty(parserResults[ProductLevel.BARCODE]))
                //_txtBoxLog.AppendText("[UPC]" + "\t\t" + _upc + Environment.NewLine + Environment.NewLine);
                System.Diagnostics.Debug.WriteLine("[UPC]" + "\t\t\t" + parserResults[ProductLevel.BARCODE] + Environment.NewLine);
        }
    }
}
